from flask import jsonify,Blueprint,request,make_response
from flask import current_app as apps
import json
from db import db1
import os

bp_com = Blueprint('com',__name__)

def get_data():
    data = request.data
    if data:
        data = json.loads(data)
    else:
        data = request.form
    return data

def xtree(data):
    list = []
    dict = {}
    if data:
        for i in data:
            i['label'] = i['name']
            dict[i['id']] = i
        for j in data:
            if j['pid'] == 0:
                j['label']=j['name']
                list.append(j)
            else:
                if 'children' not in dict[j['pid']]:
                    dict[j['pid']]['children'] = []
                dict[j['pid']]['children'].append(j)
        return list



@bp_com.route('/test')
async def test():
    str="请求ip:"+request.remote_addr +',请求的url：'+request.url+'错误信息：'+'\n'
    apps.logger.error(str)
    return "hello24324234"

@bp_com.route('/org_add',methods=['POST','GET'])
def orgadd_():
    if request.method == 'POST':
        data = get_data()
        sql1 = '''select name from organization where name ="%s" '''%(data['name'])
        d = db1.find(sql1)
        if not d:
            time1 = datetime.datetime.strftime(datetime.datetime.now(),'%Y-%m-%d %H:%M:%S')
            sql = '''insert into organization(name,coding,createpeople,createtime) values('%s','%s','%s','%s') '''\
                  %(data['name'],data['coding'],data['createpeople'],time1)
            db1.update(sql)
            return jsonify({"code":200,'mes':"添加成功"})
        else:
            return jsonify({"code":10010,'mes':"公司名已被注册"})
    else:
        sql = '''select * from organization'''
        s = db1.fid_all(sql)
        x=xtree(s)
        return jsonify({"code":200,'list':x})


#导入excel
from xlrd import open_workbook
from utils.myfile import myf
@bp_com.route('/file_add',methods = ['POST','GET'])
def file_add():
    if request.method=='POST':
        file = request.files['file']
        newname = myf.rename(file.filename)
        #上传文件
        file.save(os.path.join('./static/files/'+newname))
        name = 'static/files/'+newname
        bk = open_workbook(name, encoding_override='utf-8')
        sh = bk.sheets()[0]
        nrows = sh.nrows
        ncols = sh.ncols
        for i in range(1, nrows):
            row_date = sh.row_values(i)
            print(row_date[0])
            print(row_date[1])
            for j in range(0,ncols):
                print(row_date[j])
        return 'ok'

from io import BytesIO
from xlrd import open_workbook
from urllib.parse import quote
import xlsxwriter
import datetime
import mimetypes
#出入excel
@bp_com.route('/download', methods=["GET"])
def download():
    # 实例化二进制数据
    out = BytesIO()
    # 创建一个Excel实例
    workbook = xlsxwriter.Workbook(out)
    # 添加一个sheet，add_worksheet()，默认"Sheet1"，可自定义命名，add_worksheet("资产列表")
    table = workbook.add_worksheet()
    # table.write(行, 列, 字段名)
    # 行、列从 0 开始计数
    # 字段命名根据每个人使用情况而定，插入数据部分也是
    table.write(0, 0, "编号")
    table.write(0, 1, "部门")

    workbook.close()
    filename = quote("资产列表_{}.xlsx".format(datetime.datetime.now().strftime("%Y%m%d%H%M%S")))
    file = make_response(out.getvalue())
    out.close()
    mime_type = mimetypes.guess_type('%s.xlsx' % filename)[0]
    file.headers['Content-Type'] = mime_type
    file.headers["Cache-Control"] = "no-cache"
    file.headers['Content-Disposition'] = "attachment; filename*=utf-8''{}".format(filename)
    return file

from utils.myes import Myes
##es使用
# 创建索引(也就是插入数据)
@bp_com.route('/estestinsert')
def estest():
    es = Myes('testtabel','test-type')
    try:
        sql = "select * from organization limit 0,5"
        data = db1.find_all(sql)
        j=-1
        for i in data:
            j+=1
            es.insert(i)
        return 'true'
    except:
        print('error')
        return 'false'

# 查询数据根据字
@bp_com.route('/esget')
def esget():
    es = Myes('testtabel','test-type')
    body = {
        #查询一个字的全部数据
           "match":{
            "name":"财务"
        }
        # "multi_match":{
        #     "query":"abc",
        #     "fields":["name", "content"]
        # }
    }
    data = es.search(body)
    address_data = data['hits']['hits']
    print(address_data)
    return 'ok'
import time

@bp_com.route('/show',methods = ['POST','GET'])
def show():
    if request.method == 'GET':
        sql = '''select id,name from organization'''
        org = db1.fid_all(sql)
        sql2 = '''select id,name from job'''
        jo = db1.fid_all(sql2)
        sql3 = '''select id,name from post'''
        pos = db1.fid_all(sql3)
        return jsonify({"code":200,'org_list':org,'job_list':jo,'post_list':pos})

#获取头像
@bp_com.route('/pid',methods=['POST'])
def pid():
    file = request.files['file']
    file.save(os.path.join('./static/pic/' + file.filename))
    return jsonify({'file':file.filename})

#添加用户
@bp_com.route('/user_add',methods=['POST','GET'])
def user_add():
    if request.method=='POST':
        data = get_data()
        sql = '''insert into user(username,password,name,age,gender,mobile,email,jobnumber,organization_id,post_id,job_id,pic)
        values("%s","%s","%s",%d,%d,"%s","%s",%d,%d,%d,%d,"%s") '''%(data['username'],data['password'],data['name'],int(data['age']),
        int(data['gender']),data['mobile'],data['email'],int(data['jobnumber']),int(data['organization_id']),int(data['post_id']),int(data['job_id']),data['pic'])
        db1.update(sql)
        return jsonify({"code":200})
    else:
        sql = '''select * from user'''
        d = db1.fid_all(sql)
        return jsonify({"code":200,'list':d})


